const express = require('express');
const cors = require('cors');

// const testData = require("./test.json")
// const test = require("./test")

const mysql = require('mysql2');
const bodyParser = require('body-parser');

require('dotenv').config();
const router = express.Router();


// 创建一个连接池
const db_host =  process.env.DB_HOST
const db_user = process.env.DB_USER
const db_password = process.env.DB_PASS
const db_database =  process.env.DB_NAME

const pool = mysql.createPool({
    host: db_host,       // MySQL服务器地址
    user: db_user,               // MySQL用户名
    database: db_database,      // 要连接的数据库名
    password: db_password,       // MySQL密码
    port: 3306,              // MySQL服务器端口
    
    connectionLimit: 10,      // 连接池中允许的最大连接数
    waitForConnections: true, // 如果没有可用连接，队列等待而不是拒绝
    queueLimit: 0,             // 连接请求排队的最大数量，默认为 0，无限制
    connectTimeout: 10000,    // 连接超时时间（毫秒）
    //acquireTimeout: 10000,    // 从连接池获取连接的超时时间（毫秒）
  });

router.use(cors());
router.use(bodyParser.json());
router.use(bodyParser.urlencoded({ extended: true }));
  

var metaData = {};
var totalNum = 0;
var dbresults = [];

const sqlSchool = "select * from metadata_tb where categroy = '学校'"
const sqlMajor = "select * from metadata_tb where categroy = '专业'"
const sqlGrade = "select * from metadata_tb where categroy = '最高学历'"
const sqlWorkExp = "select * from metadata_tb where categroy = '工作经验'"

function querySchool() {
    return new Promise((reslove, reject) => {
        pool.query(sqlSchool, (err, results) => {
            console.log("sqlSchool is :" + sqlSchool)
            if (err) {
                console.log(err)
                res.json({ err: err, msg: 'fail', code: 500 })
                reject()
            }
            metaData.schools = results
            console.log("schools:", metaData.schools)
            reslove()
        })
    })
}

function queryMajor() {
    return new Promise((reslove, reject) => {
        pool.query(sqlMajor, (err, results) => {
            if (err) {
                console.log(err)
                res.json({ err: err, msg: 'fail', code: 500 })
                return;
            }
            metaData.majors = results
            console.log("majors:", metaData.majors)
            reslove()
        })
    })
}

function queryGrade() {
    return new Promise((reslove, reject) => {
        pool.query(sqlGrade, (err, results) => {
            if (err) {
                console.log(err)
                res.json({ err: err, msg: 'fail', code: 500 })
                return;
            }
            metaData.grades = results
            console.log("grades:", metaData.grades)
            reslove()
        })
    })
}

function queryWorkExp() {
    return new Promise((reslove, reject) => {
        pool.query(sqlWorkExp, (err, results) => {
            if (err) {
                console.log(err)
                res.json({ err: err, msg: 'fail', code: 500 })
                return;
            }
            metaData.workexps = results
            console.log("workexps:", metaData.workexps)
            reslove()
        })
    })
}


function queryTotalNumber(totalSql) {
    return new Promise((reslove, reject) => {
        pool.query(totalSql, (err, results) => {
            if (err) {
                console.log(err)
                res.json({ err: err, msg: 'fail', code: 500 })
                return;
            }
            totalNum = results[0].COUNT
            console.log("### total num is:"+totalNum)
            reslove()
        })
    })
}

function queryResults(querySql) {
    return new Promise((reslove, reject) => {
        pool.query(querySql, (err, results) => {
            if (err) {
                console.log(err)
                res.json({ err: err, msg: 'fail', code: 500 })
                return;
            }
            dbresults = results
            reslove()
        })
    })
}


//获取基础数据袁术
router.get('/getMetaData', (req, res) => {
    // 使用Promise.all来同步执行多个数据库操作
    Promise.all([
        querySchool(),
        queryGrade(),
        queryMajor(),
        queryWorkExp()
    ])
        .then(() => {
            res.json({ records: metaData, code: 200, msg: 'ok' })
        })
        .catch(err => {
            console.error('数据库操作出错:', err);
            res.status(500).send('数据库操作出错');
        });

});

//查询过滤学校
router.get('/querySchools', (req, res) => {
    const params = req.query;
    console.log(params)
    let condtion = ''
    if (params.schoolKey !== '' && params.schoolKey !== undefined) {
        condtion = condtion + " and tagname  like '%" + params.schoolKey + "%'"
    }
    let fiterSchoolSql = "select * from metadata_tb where categroy='学校'"
    fiterSchoolSql = fiterSchoolSql + condtion;

    pool.query(fiterSchoolSql, (err, result) => {
        if (err) {
            console.log(err)
            res.json({ err: err, msg: '查询学校失败', code: 500 })
            return
        }
        console.log("filter school ok sql is -> :"+fiterSchoolSql)
        res.json({ msg: '查询学校成功', data:result, code: 200 })
    })
});

//查询过滤专业
router.get('/queryMajors', (req, res) => {
    const params = req.query;
    console.log(params)
    let condtion = ''
    if (params.majorKey !== '' && params.majorKey !== undefined) {
        condtion = condtion + " and tagname  like '%" + params.majorKey + "%'"
    }
    let fiterMajorSql = "select * from metadata_tb where categroy='专业'"
    fiterMajorSql = fiterMajorSql + condtion;

    pool.query(fiterMajorSql, (err, result) => {
        if (err) {
            console.log(err)
            res.json({ err: err, msg: '查询专业失败', code: 500 })
            return
        }
        console.log("filter major ok sql is -> :"+fiterMajorSql)
        res.json({ msg: '查询专业成功', data:result, code: 200 })
    })
})


//查询过滤年级
router.get('/queryGrades', (req, res) => {
    const params = req.query;
    console.log(params)
    let condtion = ''
    if (params.gradeKey !== '' && params.gradeKey !== undefined) {
        condtion = condtion + " and tagname  like '%" + params.gradeKey + "%'"
    }
    let fiterGradeSql = "select * from metadata_tb where categroy='最高学历'"
    fiterGradeSql = fiterGradeSql + condtion;

    pool.query(fiterGradeSql, (err, result) => {
        if (err) {
            console.log(err)
            res.json({ err: err, msg: '查询学历失败', code: 500 })
            return
        }
        console.log("filter grade ok sql is -> :"+fiterGradeSql)
        res.json({ msg: '查询学历成功', data:result, code: 200 })
    })
})


//查询过滤年级
router.get('/queryWorkexps', (req, res) => {
    const params = req.query;
    console.log(params)
    let condtion = ''
    if (params.workexpKey !== '' && params.workexpKey !== undefined) {
        condtion = condtion + " and tagname  like '%" + params.workexpKey + "%'"
    }
    let fiterWorkexpSql = "select * from metadata_tb where categroy='工作经验'"
    fiterWorkexpSql = fiterWorkexpSql + condtion;

    pool.query(fiterWorkexpSql, (err, result) => {
        if (err) {
            console.log(err)
            res.json({ err: err, msg: '查询工作经验失败', code: 500 })
            return
        }
        console.log("filter grade ok sql is -> :"+fiterWorkexpSql)
        res.json({ msg: '查询工作经验成功', data:result, code: 200 })
    })
})


//实现前端带参数的查询
router.get('/query', (req, res) => {

    const params = req.query;
    // console.log(params);

    const page = parseInt(req.query.page) || 1 //当前页
    const pageSize = parseInt(req.query.pageSize) || 10; //每页大小
    const offset = (page - 1) * pageSize;  //页面偏移量开始

    var querySql = "select * from advice_tb ";
    
    let condtion = ' where '
    if (params.searchQuery !== '' && params.searchQuery !== undefined) {
        condtion = condtion + " content like '%" + params.searchQuery + "%'"
    } else {
        condtion = condtion + " content like '%%'"
    }
    if (params.selectedGrade !== '' && params.selectedGrade !== undefined) {
        condtion = condtion + " and education ='" + params.selectedGrade + "'"
    }
    if (params.selectedSchool !== '' && params.selectedSchool !== undefined) {
        condtion = condtion + " and school ='" + params.selectedSchool + "'"
    }
    if (params.selectedWorkExp !== '' && params.selectedWorkExp !== undefined) {
        condtion = condtion + " and work_exp ='" + params.selectedWorkExp + "'"
    }
    if (params.selectedmajor !== '' && params.selectedmajor !== undefined) {
        condtion = condtion + " and major ='" + params.selectedmajor + "'"
    }
    let orderby = " order by create_time desc "


    //let totalNum = 0// 从数据里先查询到最大数量
    var totalNumSql = "select count(ID) as COUNT from advice_tb" + condtion
    console.log("### totalNumSql ####  :" + totalNumSql)

    // pool.query(totalNumSql, (err, results, fields) => {
    //     if (err) {
    //         console.log(err)
    //         res.json({ err: err, msg: 'fail', code: 500 })
    //         return;
    //     }
    //     console.log(results)
    //     totalNum = results[0].COUNT
    //     console.log("totalNum is :" + totalNum)
    //     console.log("MaxPage is :" + parseInt(totalNum / pageSize + 1))
    //     //res.json({records:resul ts,code:200,msg:'ok'})
    // })

    querySql = querySql + condtion + orderby + ` limit ${pageSize} offset ${offset}`
    console.log("#### querySql #### is :" + querySql)

 
    Promise.all([
        queryTotalNumber(totalNumSql),
        queryResults(querySql)
    ])
        .then(() => {
            res.json({ records: dbresults,total: totalNum, code: 200, msg: 'ok' })
        })
        .catch(err => {
            console.error('数据库操作出错:', err);
            res.status(500).send('数据库操作出错');
        });

});


router.post("/addAdvice", (req, res) => {
    console.log(req.body)

    //插入数据库 
    var sql = "insert into advice_tb ("
        + "`content`,"
        + "`school`,"
        + "`major`,"
        + "`education`,"
        + "`work_exp`,"
        + "`review_status`,"
        + "`view_num`,"
        + "`create_time`,"
        + "`update_time`) values(?,?,?,?,?,?,?,?,?) "

    console.log("sql is: " + sql)

    const { school, advice, grade, workexp, major } = req.body

    var reviewStatus = 0
    var viewNum = 0
    var create_time = new Date();
    var updateTime = new Date();

    var fields = [advice, school, major, grade, workexp, reviewStatus, viewNum, create_time, updateTime]

    pool.query(sql, fields, (err, result) => {
        if (err) {
            console.log(err)
            res.json({ err: err, msg: '添加建议失败', code: 500 })
            return
        }
        console.log("insert table ok")
        res.json({ msg: '添加建议成功', code: 200 })
    })
});

//导出
module.exports = router;